﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;

//using MySql.Data.MySqlClient;
using AsianHouse.Common;
using AsianHouse.Common.Interfaces;
using AsianHouse.Common.Objects;
using MySql.Data.MySqlClient;


namespace AsianHouse.DataLogic
{
    public class Data
    {
        static readonly string connectionString = ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;

        public static int CreateUser(string userName, string password, string roles, string emailAddress)
        {
            var csBuilder = new MySqlConnectionStringBuilder(connectionString) { UseProcedureBodies = false };

            var connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            try
            {

                var cmd = new MySqlCommand("CreateUser", connection);

                //UserID           int(10) unsigned PK (not set)
                //UserName         varchar(45)
                //Roles            varchar(45)
                //Password         varchar(100)
                //CreationDate     timestamp (not set)
                //EmailAddress     varchar(100)

                var param = new MySqlParameter("UserName", MySqlDbType.String) { Value = userName };
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Password", MySqlDbType.String) { Value = SecurityManager.HashPassword(password) };
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Roles", MySqlDbType.String) { Value = roles };
                cmd.Parameters.Add(param);

                param = new MySqlParameter("EmailAddress", MySqlDbType.String) { Value = emailAddress };
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteNonQuery();

            }
            finally
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public static UserIdentity GetUserIdentity(string userName_, string password_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            var hashedPswd = SecurityManager.HashPassword(password_);


            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            UserIdentity identity = new UserIdentity();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetUserIdentity", connection);

                MySqlParameter param = new MySqlParameter("userPassword", MySqlDbType.String);
                param.Value = hashedPswd;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("userName", MySqlDbType.String);
                param.Value = userName_;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;


                using (MySqlDataReader userData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (userData.Read())
                    {

                        identity = new UserIdentity
                            (
                                Convert.ToString(userData["UserName"]), 
                                Convert.ToString(userData["EmailAddress"]),
                                Convert.ToString(userData["Roles"])
                            );
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return identity;
        }

        public static IItemGroup GetItemGroup(int itemGroupID_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;


            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            ItemGroup itemGroup = new ItemGroup();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetItemGroup", connection);

                MySqlParameter param = new MySqlParameter("itemGroupId", MySqlDbType.Int32);
                param.Value = itemGroupID_;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;

                

                using (MySqlDataReader itemGroupData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (itemGroupData.Read())
                    {

                        itemGroup = new ItemGroup();

                        itemGroup.GroupName = Convert.ToString(itemGroupData["GroupName"]);

                        itemGroup.Id = Convert.ToInt32(itemGroupData["Id"]);

                        itemGroup.ImageFile = Convert.ToString(itemGroupData["ImageFile"]);

                        itemGroup.Priority = Convert.ToInt32(itemGroupData["Priority"]);
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return itemGroup;
        }

        public static List<IItem> SearchForItems(string searchString_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            List<IItem> items = new List<IItem>();

            try
            {

                MySqlCommand cmd = new MySqlCommand("SearchForItems", connection);

                MySqlParameter param = new MySqlParameter("searchString", MySqlDbType.String);
                param.Value = searchString_;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;

                

                using (MySqlDataReader itemGroupData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (itemGroupData.Read())
                    {
                        IItem item = new Item();

                        item.CreationDate = Convert.ToDateTime(itemGroupData["CreationDate"]);
                        item.CurrencyCode = Convert.ToString(itemGroupData["Valuta_Kode"]);
                        item.Description = Convert.ToString(itemGroupData["Description"]);
                        if (itemGroupData["Guid"] != DBNull.Value && itemGroupData["Guid"].ToString() != "")
                        {
                            item.Guid = new Guid(Convert.ToString(itemGroupData["Guid"]));
                        }

                        if (itemGroupData["Height"] != DBNull.Value)
                        {
                            item.Height = Convert.ToDouble(itemGroupData["Height"]);
                        }
                        item.IsActive = Convert.ToBoolean(itemGroupData["Active"]);
                        item.ItemGroupID = Convert.ToInt32(itemGroupData["ItemGroup"]);
                        item.Name = Convert.ToString(itemGroupData["ItemName_DK"]);
                        item.ImageFile = Convert.ToString(itemGroupData["ImageFile"]);

                        if (itemGroupData["Price"] != DBNull.Value)
                        {
                            item.Price = Convert.ToDouble(itemGroupData["Price"]);
                        }
                        item.SKU = Convert.ToString(itemGroupData["ItemNumber"]);
                        item.SortOrder = Convert.ToInt32(itemGroupData["SortOrder"]);
                        if (itemGroupData["Weight"] != DBNull.Value)
                        {
                            item.Weight = Convert.ToDouble(itemGroupData["Weight"]);
                        }

                        if (itemGroupData["Width"] != DBNull.Value)
                        {
                            item.Width = Convert.ToDouble(itemGroupData["Width"]);
                        }

                        if (itemGroupData["Length"] != DBNull.Value)
                        {
                            item.Length = Convert.ToDouble(itemGroupData["Length"]);
                        }

                        items.Add(item);
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return items;
        }

        public static IItem GetItem(Guid guid_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            IItem item = new Item();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetItem", connection);

                MySqlParameter param = new MySqlParameter("Guid", MySqlDbType.String);
                param.Value = guid_;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;

                ItemGroup itemGroup = new ItemGroup();

                

                using (MySqlDataReader itemData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (itemData.Read())
                    {
                        item.CreationDate = Convert.ToDateTime(itemData["CreationDate"]);
                        item.CurrencyCode = Convert.ToString(itemData["Valuta_Kode"]);
                        item.Description = Convert.ToString(itemData["Description"]);
                        if (itemData["Guid"] != DBNull.Value && itemData["Guid"].ToString() != "")
                        {
                            item.Guid = new Guid(Convert.ToString(itemData["Guid"]));
                        }

                        if (itemData["Height"] != DBNull.Value)
                        {
                            item.Height = Convert.ToDouble(itemData["Height"]);
                        }
                        item.IsActive = Convert.ToBoolean(itemData["Active"]);
                        item.ItemGroupID = Convert.ToInt32(itemData["ItemGroup"]);
                        item.Name = Convert.ToString(itemData["ItemName_DK"]);
                        item.ImageFile = Convert.ToString(itemData["ImageFile"]);

                        if (itemData["Price"] != DBNull.Value)
                        {
                            item.Price = Convert.ToDouble(itemData["Price"]);
                        }
                        item.SKU = Convert.ToString(itemData["ItemNumber"]);
                        item.SortOrder = Convert.ToInt32(itemData["SortOrder"]);
                        if (itemData["Weight"] != DBNull.Value)
                        {
                            item.Weight = Convert.ToDouble(itemData["Weight"]);
                        }

                        if (itemData["Width"] != DBNull.Value)
                        {
                            item.Width = Convert.ToDouble(itemData["Width"]);
                        }

                        if (itemData["Length"] != DBNull.Value)
                        {
                            item.Length = Convert.ToDouble(itemData["Length"]);
                        }
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }


            return item;
        }

        public static void DeleteItem(Guid guid_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            try
            {

                MySqlCommand cmd = new MySqlCommand("DeleteItem", connection);

                MySqlParameter param = new MySqlParameter("guid", MySqlDbType.String);
                param.Value = guid_.ToString();
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public static void UpdateItem(IItem item_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            try
            {

                MySqlCommand cmd = new MySqlCommand("UpdateItem", connection);

                //IN ItemNumber varchar(50) ,
                //IN ItemName_DK varchar(200),
                //IN Price decimal(18,2),
                //IN Height varchar(50),
                //IN Length varchar(50),
                //IN Width varchar(50),
                //IN Weight varchar(50),
                //IN Description longtext,
                //IN ItemGroup int(10),
                //IN Guid varchar(36),
                //IN SortOrder int(11),
                //IN ImageFile varchar(100)

                MySqlParameter param = new MySqlParameter("ItemNumber", MySqlDbType.String);
                param.Value = item_.SKU;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ItemName_DK", MySqlDbType.String);
                param.Value = item_.Name;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Price", MySqlDbType.Double);
                param.Value = item_.Price;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Height", MySqlDbType.Double);
                param.Value = item_.Height;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Length", MySqlDbType.Double);
                param.Value = item_.Length;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Width", MySqlDbType.Double);
                param.Value = item_.Width;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Weight", MySqlDbType.Double);
                param.Value = item_.Weight;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Description", MySqlDbType.String);
                param.Value = item_.Description;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ItemGroup", MySqlDbType.Int32);
                param.Value = item_.ItemGroupID;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Guid", MySqlDbType.String);
                param.Value = item_.Guid.ToString();
                cmd.Parameters.Add(param);

                param = new MySqlParameter("SortOrder", MySqlDbType.Int32);
                param.Value = item_.SortOrder;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ImageFile", MySqlDbType.String);
                param.Value = item_.ImageFile;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public static void CreateItem(IItem item_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            try
            {

                MySqlCommand cmd = new MySqlCommand("CreateItem", connection);

                //IN ItemNumber varchar(50) ,
                //IN ItemName_DK varchar(200),
                //IN Price decimal(18,2),
                //IN Height varchar(50),
                //IN Length varchar(50),
                //IN Width varchar(50),
                //IN Weight varchar(50),
                //IN Description longtext,
                //IN ItemGroup int(10),
                //IN Guid varchar(36),
                //IN SortOrder int(11),
                //IN ImageFile varchar(100)

                MySqlParameter param = new MySqlParameter("ItemNumber", MySqlDbType.String);
                param.Value = item_.SKU;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ItemName_DK", MySqlDbType.String);
                param.Value = item_.Name;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Price", MySqlDbType.Double);
                param.Value = item_.Price;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Height", MySqlDbType.Double);
                param.Value = item_.Height;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Length", MySqlDbType.Double);
                param.Value = item_.Length;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Width", MySqlDbType.Double);
                param.Value = item_.Width;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Weight", MySqlDbType.Double);
                param.Value = item_.Weight;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Description", MySqlDbType.String);
                param.Value = item_.Description;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ItemGroup", MySqlDbType.Int32);
                param.Value = item_.ItemGroupID;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("Guid", MySqlDbType.String);
                param.Value = item_.Guid.ToString();
                cmd.Parameters.Add(param);

                param = new MySqlParameter("SortOrder", MySqlDbType.Int32);
                param.Value = item_.SortOrder;
                cmd.Parameters.Add(param);

                param = new MySqlParameter("ImageFile", MySqlDbType.String);
                param.Value = item_.ImageFile;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();

            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public static List<IItem> GetItemsForGroup(int itemGroupID_)
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            List<IItem> items = new List<IItem>();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetItemsForGroup", connection);

                MySqlParameter param = new MySqlParameter("itemGroupID", MySqlDbType.Int32);
                param.Value = itemGroupID_;
                cmd.Parameters.Add(param);

                cmd.CommandType = CommandType.StoredProcedure;

                

                using (MySqlDataReader itemGroupData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (itemGroupData.Read())
                    {
                        IItem item = new Item();

                        item.CreationDate = Convert.ToDateTime(itemGroupData["CreationDate"]);
                        item.CurrencyCode = Convert.ToString(itemGroupData["Valuta_Kode"]);
                        item.Description = Convert.ToString(itemGroupData["Description"]);
                        if (itemGroupData["Guid"] != DBNull.Value && itemGroupData["Guid"].ToString() != "")
                        {
                            item.Guid = new Guid(Convert.ToString(itemGroupData["Guid"]));
                        }

                        if (itemGroupData["Height"] != DBNull.Value)
                        {
                            item.Height = Convert.ToDouble(itemGroupData["Height"]);
                        }
                        item.IsActive = Convert.ToBoolean(itemGroupData["Active"]);
                        item.ItemGroupID = Convert.ToInt32(itemGroupData["ItemGroup"]);
                        item.Name = Convert.ToString(itemGroupData["ItemName_DK"]);
                        item.ImageFile = Convert.ToString(itemGroupData["ImageFile"]);

                        if (itemGroupData["Price"] != DBNull.Value)
                        {
                            item.Price = Convert.ToDouble(itemGroupData["Price"]);
                        }
                        item.SKU = Convert.ToString(itemGroupData["ItemNumber"]);
                        item.SortOrder = Convert.ToInt32(itemGroupData["SortOrder"]);
                        if (itemGroupData["Weight"] != DBNull.Value)
                        {
                            item.Weight = Convert.ToDouble(itemGroupData["Weight"]);
                        }

                        if (itemGroupData["Width"] != DBNull.Value)
                        {
                            item.Width = Convert.ToDouble(itemGroupData["Width"]);
                        }

                        if (itemGroupData["Length"] != DBNull.Value)
                        {
                            item.Length = Convert.ToDouble(itemGroupData["Length"]);
                        }

                        items.Add(item);
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return items;
        }

        public static List<ItemGroup> GetItemGroups()
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            List<ItemGroup> itemGroups = new List<ItemGroup>();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetItemGroups", connection);

                cmd.CommandType = CommandType.StoredProcedure;

                

                using (MySqlDataReader itemGroupData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (itemGroupData.Read())
                    {

                        ItemGroup itemGroup = new ItemGroup();

                        itemGroup.GroupName = Convert.ToString(itemGroupData["GroupName"]);

                        itemGroup.Id = Convert.ToInt32(itemGroupData["Id"]);

                        itemGroup.ImageFile = Convert.ToString(itemGroupData["ImageFile"]);

                        itemGroup.Priority = Convert.ToInt32(itemGroupData["Priority"]);

                        itemGroups.Add(itemGroup);

                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return itemGroups;
        }

        public static List<Exhibition> GetExhibitionDates()
        {
            MySqlConnectionStringBuilder csBuilder = new MySqlConnectionStringBuilder(connectionString);
            csBuilder.UseProcedureBodies = false;

            MySqlConnection connection = new MySqlConnection(csBuilder.ToString());
            connection.Open();

            List<Exhibition> exhibitions = new List<Exhibition>();

            try
            {

                MySqlCommand cmd = new MySqlCommand("GetExhibitions", connection);

                cmd.CommandType = CommandType.StoredProcedure;

                

                using (MySqlDataReader exhibitionData = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (exhibitionData.Read())
                    {
                        exhibitions.Add(
                            new Exhibition
                            {
                                EndDate = Convert.ToDateTime(exhibitionData["SlutDato"]),
                                Hall = exhibitionData["Hal"].ToString(),
                                ImageFile = exhibitionData["Billede"].ToString(),
                                IsActive = Convert.ToBoolean(exhibitionData["aktiv"]),
                                Location = exhibitionData["MesseSted"].ToString(),
                                Spot = exhibitionData["Stand"].ToString(),
                                StartDate = Convert.ToDateTime(exhibitionData["StartDato"]),
                                SubTitle = exhibitionData["UnderTitel"].ToString(),
                                Title = exhibitionData["MesseTitel"].ToString(),
                            }
                            );
                    }
                }
            }
            finally
            {
                if (connection.State != ConnectionState.Open && connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return exhibitions;
        }

    }
}
